﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using Aspose.Excel;
using Telerik.Web.UI;

namespace LenderClearingHouse
{
    public partial class NetSheetDialog : System.Web.UI.Page
    {
        private static string printUrl = "NetSheetPrint.aspx?PropertyId={0}&PrintView=1&IsLoan={1}";
        private static string excelUrl = "NetSheetDialog.aspx?PropertyId={0}&ExcelView=1&IsLoan={1}";
        private static string printUrlOld = "NetSheetDialog.aspx?PropertyId={0}&PrintView=1&IsLoan={1}";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataAccess da = new DataAccess();
                DataTable dt = da.GetDataTable("getNetSheet", new string[] { "@PropertyId" }, new object[] { GetPropertyId() });

                tbSellingPrice.Value = Convert.ToDouble(dt.Rows[0]["BuyerPurchasePrice"]);
                tbRehabCostEstimated.Value = Convert.ToDouble(dt.Rows[0]["RehabCosts"]);
                tbPurchasePriceEstimated.Value = Convert.ToDouble(dt.Rows[0]["PurchasePrice"]);

                tbLockEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LockChange_Estimated"]);
                tbLockActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LockChange_Actual"]);
                tbLockLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LockChange_WithLoan"]);

                tbReserveEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ReserveForUtilites_Estimated"]);
                tbReserveActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ReserveForUtilites_Actual"]);
                tbReserveLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ReserveForUtilites_WithLoan"]);

                tbInsuranceEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Insurance_Estimated"]);
                tbInsuranceActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Insurance_Actual"]);
                tbInsuranceLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Insurance_WithLoan"]);
 
                tbCashEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_CashForKeys_Estimated"]);
                tbCashActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_CashForKeys_Actual"]);
                tbCashLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_CashForKeys_WithLoan"]);

                tbMiscEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Miscellaenous_Estimated"]);
                tbMiscActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Miscellaenous_Actual"]);
                tbMiscLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Miscellaenous_WithLoan"]);
                    
                tbBrokerPercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_BrokerCoop"]);
                tbSellerPercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosingIncentives"]);
                tbEscrowPercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_EscrowClosingEstimate"]);    
                
                tbSellersCostEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellersCostRepairs_Estimated"]);
                tbSellersCostActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellersCostRepairs_Actual"]);
                tbSellersCostLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellersCostRepairs_WithLoan"]);

                tbHomeEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_HomeWarranty_Estimated"]);
                tbHomeActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_HomeWarranty_Actual"]);
                tbHomeLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_HomeWarranty_WithLoan"]);

                tbAppraisalEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Appraisal_Estimated"]);
                tbAppraisalActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Appraisal_Actual"]);
                tbAppraisalLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Appraisal_WithLoan"]);
                
                tbMiscClosingEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_MiscellaneousClosingCosts_Estimated"]);
                tbMiscClosingActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_MiscellaneousClosingCosts_Actual"]);
                tbMiscClosingLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_MiscellaneousClosingCosts_WithLoan"]);
                
                tbHoaEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_HOADues_Estimated"]);
                tbHoaActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_HOADues_Actual"]);
                tbHoaLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_HOADues_WithLoan"]);

                tbTransactionEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_TransactionFee_Estimated"]);
                tbTransactionActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_TransactionFee_Actual"]);
                tbTransactionLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_TransactionFee_WithLoan"]);

                tbOtherEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Other_Estimated"]);
                tbOtherActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Other_Actual"]);
                tbOtherLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Other_WithLoan"]);
                tbProfitParticipationPercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_FeeLCH"]);

                tbNonProfitFeePercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee"]);
                tbClosingCostsPercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts"]);
                tbLoanBrokerFee.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanBrokerFee"]);
                tbLoanFeePercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanFee"]);
                tbLoanInterestPercent.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanInterest"]);

                //tbRehabCostEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_RehabCost_Estimated"]);
                tbRehabCostActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_RehabCost_Actual"]);
                tbRehabCostLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_RehabCost_WithLoan"]);
                //lblRehabSum.Text = ((tbRehabCostEstimated.Value ?? 0) + (tbRehabCostActual.Value ?? 0) + (tbRehabCostLoan.Value ?? 0)).ToString("c");

                tb1stDOT.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_1stDOT"]);
                tbDownPayment.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_DownPayment"]);
                                
                cbMortgageType.SelectedValue = dt.Rows[0]["NetSheet_MortgageType"].ToString();
                lblMortgageType.Text = dt.Rows[0]["NetSheet_MortgageType"].ToString();
                tbEstClosingDate.Text = dt.Rows[0].IsNull("NetSheet_EstClosingDate") ? "" : dt.Rows[0]["NetSheet_EstClosingDate"].ToString();

                chbWithLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_WithLoan"]);

                #region ManualChecks
                ////////////////////
                chbManualPurchasePriceActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_PurchasePrice_Actual"]);
                if (chbManualPurchasePriceActual.Checked)
                    tbPurchasePriceActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_PurchasePrice_Actual"]);
                chbManualPurchasePriceLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_PurchasePrice_WithLoan"]);
                if (chbManualPurchasePriceLoan.Checked)
                    tbPurchasePriceLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_PurchasePrice_WithLoan"]);

                chbManualClosingCostsEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ClosingCosts_Estimated"]);
                if (chbManualClosingCostsEstimated.Checked)
                    tbClosingCostsEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts_Estimated"]);
                chbManualClosingCostsActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ClosingCosts_Actual"]);
                if (chbManualClosingCostsActual.Checked)
                    tbClosingCostsActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts_Actual"]);
                chbManualClosingCostsLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ClosingCosts_WithLoan"]);
                if (chbManualClosingCostsLoan.Checked)
                    tbClosingCostsLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts_WithLoan"]);

                chbManualNonProfitFeeEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NonProfitFee_Estimated"]);
                if (chbManualNonProfitFeeEstimated.Checked)
                    tbNonProfitFeeEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee_Estimated"]);
                chbManualNonProfitFeeActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NonProfitFee_Actual"]);
                if (chbManualNonProfitFeeActual.Checked)
                    tbNonProfitFeeActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee_Actual"]);
                chbManualNonProfitFeeLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NonProfitFee_WithLoan"]);
                if (chbManualNonProfitFeeLoan.Checked)
                    tbNonProfitFeeLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee_WithLoan"]);

                chbManualAcquisitionEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_AcquisitionCost_Estimated"]);
                if (chbManualAcquisitionEstimated.Checked)
                    tbAcquisitionEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_AcquisitionCost_Estimated"]);
                chbManualAcquisitionActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_AcquisitionCost_Actual"]);
                if (chbManualAcquisitionActual.Checked)
                    tbAcquisitionActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_AcquisitionCost_Actual"]);
                chbManualAcquisitionLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_AcquisitionCost_WithLoan"]);
                if (chbManualAcquisitionLoan.Checked)
                    tbAcquisitionLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_AcquisitionCost_WithLoan"]);
                ////////////////////
                chbManualPreparationEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_PreparationCost_Estimated"]);
                if (chbManualPreparationEstimated.Checked)
                    tbPreparationEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_PreparationCost_Estimated"]);
                chbManualPreparationActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_PreparationCost_Actual"]);
                if (chbManualPreparationActual.Checked)
                    tbPreparationActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_PreparationCost_Actual"]);
                chbManualPreparationLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_PreparationCost_WithLoan"]);
                if (chbManualPreparationLoan.Checked)
                    tbPreparationLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_PreparationCost_WithLoan"]);

                chbManualTotalPresaleEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_PresaleCost_Estimated"]);
                if (chbManualTotalPresaleEstimated.Checked)
                    tbTotalPresaleEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_PresaleCost_Estimated"]);
                chbManualTotalPresaleActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_PresaleCost_Actual"]);
                if (chbManualTotalPresaleActual.Checked)
                    tbTotalPresaleActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_PresaleCost_Actual"]);
                chbManualTotalPresaleLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_PresaleCost_WithLoan"]);
                if (chbManualTotalPresaleLoan.Checked)
                    tbTotalPresaleLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_PresaleCost_WithLoan"]);
                ////////////////////
                chbManualSellingPriceEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellingPrice_Estimated"]);
                if (chbManualSellingPriceEstimated.Checked)
                    tbSellingPriceEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellingPrice_Estimated"]);
                chbManualSellingPriceActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellingPrice_Actual"]);
                if (chbManualSellingPriceActual.Checked)
                    tbSellingPriceActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellingPrice_Actual"]);
                chbManualSellingPriceLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellingPrice_WithLoan"]);
                if (chbManualSellingPriceLoan.Checked)
                    tbSellingPriceLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellingPrice_WithLoan"]);

                chbManualBrokerEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Broker_Estimated"]);
                if (chbManualBrokerEstimated.Checked)
                    tbBrokerEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Broker_Estimated"]);
                chbManualBrokerActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Broker_Actual"]);
                if (chbManualBrokerActual.Checked)
                    tbBrokerActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Broker_Actual"]);
                chbManualBrokerLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Broker_WithLoan"]);
                if (chbManualBrokerLoan.Checked)
                    tbBrokerLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Broker_WithLoan"]);

                chbManualSellerClosingEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellerClosing_Estimated"]);
                if (chbManualSellerClosingEstimated.Checked)
                    tbSellerClosingEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosing_Estimated"]);
                chbManualSellerClosingActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellerClosing_Actual"]);
                if (chbManualSellerClosingActual.Checked)
                    tbSellerClosingActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosing_Actual"]);
                chbManualSellerClosingLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_SellerClosing_WithLoan"]);
                if (chbManualSellerClosingLoan.Checked)
                    tbSellerClosingLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_SellerClosing_WithLoan"]);

                chbManualEscrowEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Escrow_Estimated"]);
                if (chbManualEscrowEstimated.Checked)
                    tbEscrowEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Escrow_Estimated"]);
                chbManualEscrowActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Escrow_Actual"]);
                if (chbManualEscrowActual.Checked)
                    tbEscrowActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Escrow_Actual"]);
                chbManualEscrowLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Escrow_WithLoan"]);
                if (chbManualEscrowLoan.Checked)
                    tbEscrowLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Escrow_WithLoan"]);

                chbManualTotalClosingCostsEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_ClosingCost_Estimated"]);
                if (chbManualTotalClosingCostsEstimated.Checked)
                    tbTotalClosingCostsEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_ClosingCost_Estimated"]);
                chbManualTotalClosingCostsActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_ClosingCost_Actual"]);
                if (chbManualTotalClosingCostsActual.Checked)
                    tbTotalClosingCostsActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_ClosingCost_Actual"]);
                chbManualTotalClosingCostsLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_ClosingCost_WithLoan"]);
                if (chbManualTotalClosingCostsLoan.Checked)
                    tbTotalClosingCostsLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_ClosingCost_WithLoan"]);
                ////////////////////
                chbManualPurchasePlusRehab.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Note"]);
                if (chbManualPurchasePlusRehab.Checked)
                    tbPurchasePlusRehab.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Note"]);

                chbManual2ndDOT.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_2ndDOT"]);
                if (chbManual2ndDOT.Checked)
                    tb2ndDOT.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_2ndDOT"]);

                chbManualLoanFee.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_LoanFee"]);
                if (chbManualLoanFee.Checked)
                    tbLoanFeeCalculated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanFeeSumma"]);

                chbManualLoanInterest.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_LoanInterest"]);
                if (chbManualLoanInterest.Checked)
                    tbLoanInterestCalculated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_LoanInterestSumma"]);

                chbManualLoanTotal.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_LoanCost"]);
                if (chbManualLoanTotal.Checked)
                    tbLoanTotal.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_LoanCost"]);
                ////////////////////
                chbManualNetProfitEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NetProfit_Estimated"]);
                if (chbManualNetProfitEstimated.Checked)
                    tbNetProfitEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NetProfit_Estimated"]);
                chbManualNetProfitActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NetProfit_Actual"]);
                if (chbManualNetProfitActual.Checked)
                    tbNetProfitActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NetProfit_Actual"]);
                chbManualNetProfitLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_NetProfit_WithLoan"]);
                if (chbManualNetProfitLoan.Checked)
                    tbNetProfitLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_NetProfit_WithLoan"]);

                chbManualProfitParticipationEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ProfitParticipation_Estimated"]);
                if (chbManualProfitParticipationEstimated.Checked)
                    tbProfitParticipationEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ProfitParticipation_Estimated"]);
                chbManualProfitParticipationActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ProfitParticipation_Actual"]);
                if (chbManualProfitParticipationActual.Checked)
                    tbProfitParticipationActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ProfitParticipation_Actual"]);
                chbManualProfitParticipationLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_ProfitParticipation_WithLoan"]);
                if (chbManualProfitParticipationLoan.Checked)
                    tbProfitParticipationLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_ProfitParticipation_WithLoan"]);

                chbManualEquityEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_Equity_Estimated"]);
                if (chbManualEquityEstimated.Checked)
                    tbEquityEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_Equity_Estimated"]);
                chbManualEquityActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_Equity_Actual"]);
                if (chbManualEquityActual.Checked)
                    tbEquityActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_Equity_Actual"]);
                chbManualEquityLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_Equity_WithLoan"]);
                if (chbManualEquityLoan.Checked)
                    tbEquityLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_Equity_WithLoan"]);

                chbManualTotalNetProfitEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_NetProfit_Estimated"]);
                if (chbManualTotalNetProfitEstimated.Checked)
                    tbTotalNetProfitEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_NetProfit_Estimated"]);
                chbManualTotalNetProfitActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_NetProfit_Actual"]);
                if (chbManualTotalNetProfitActual.Checked)
                    tbTotalNetProfitActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_NetProfit_Actual"]);
                chbManualTotalNetProfitLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_NetProfit_WithLoan"]);
                if (chbManualTotalNetProfitLoan.Checked)
                    tbTotalNetProfitLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_NetProfit_WithLoan"]);

                chbManualTotalNetPercentEstimated.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_NetPercent_Estimated"]);
                if (chbManualTotalNetPercentEstimated.Checked)
                    tbTotalNetPercentEstimated.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_NetPercent_Estimated"]);
                chbManualTotalNetPercentActual.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_NetPercent_Actual"]);
                if (chbManualTotalNetPercentActual.Checked)
                    tbTotalNetPercentActual.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_NetPercent_Actual"]);
                chbManualTotalNetPercentLoan.Checked = Convert.ToBoolean(dt.Rows[0]["NetSheet_Manual_Total_NetPercent_WithLoan"]);
                if (chbManualTotalNetPercentLoan.Checked)
                    tbTotalNetPercentLoan.Value = Convert.ToDouble(dt.Rows[0]["NetSheet_Total_NetPercent_WithLoan"]);
                ////////////////////
                #endregion
                
                ddlOccupancy.SelectedIndex = 0;
                if (!dt.Rows[0].IsNull("NetSheet_Occupancy"))
                    if (ddlOccupancy.FindItemByValue(dt.Rows[0]["NetSheet_Occupancy"].ToString()) != null)
                        ddlOccupancy.SelectedValue = dt.Rows[0]["NetSheet_Occupancy"].ToString();

                if (!dt.Rows[0].IsNull("NetSheet_AcquisitionDate"))
                    tbAcquisitionDate.Text = Convert.ToDateTime(dt.Rows[0]["NetSheet_AcquisitionDate"]).ToString("MM/dd/yyyy");
                                
                RefreshClosingCost();
                RefreshLoan();
                Calculate();

                edPropertyLocation.Text = Convert.ToString(Session["PropertyLocation"]);
                edInvestorName.Text = Convert.ToString(Session["InvestorName"]);
                edBedrooms.Text = Convert.ToString(Session["Bedrooms"]) + "/"
                    + Convert.ToString(Session["Bathrooms"]);
                edConstructYear.Text = Convert.ToString(Session["ConstructYear"]);
                edGarageSqft.Text = Convert.ToString(Session["GarageSqft"]);
                edPool.Text = Convert.ToString(Session["Pool"]);
                edTotalSqft.Text = Convert.ToString(Session["TotalSqft"]);

                lnkPrint.NavigateUrl = string.Format(printUrl, GetPropertyId().ToString(), chbWithLoan.Checked ? "1" : "0");
                lnkPrintOld.NavigateUrl = string.Format(printUrlOld, GetPropertyId().ToString(), chbWithLoan.Checked ? "1" : "0");
                lnkDownload.NavigateUrl = string.Format(excelUrl, GetPropertyId().ToString(), chbWithLoan.Checked ? "1" : "0");
                
                if (IsExcelView())
                    ToExcel();
            }            
        }

        protected void Page_PreRender(object sender, EventArgs e)
        {
          
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            Calculate();
            if (!chbWithLoan.Checked)
                Session["NetProfit"] = Convert.ToInt32(tbTotalNetProfitActual.Value.Value);
            else
                Session["NetProfit"] = Convert.ToInt32(tbTotalNetProfitLoan.Value.Value);

            Session["BuyerPurchasePrice"] = Convert.ToInt32(tbPurchasePriceEstimated.Value.Value);
            Session["RehabCosts"] = Convert.ToInt32(tbRehabCostEstimated.Value.Value);

            int? buyerPurchasePrice = null;
            if(BuyerPurchasePrice().HasValue)
                buyerPurchasePrice = Convert.ToInt32(tbSellingPrice.Value.Value);//Convert.ToInt32(tbPurchasePriceEstimated.Value.Value);

            int? purchasePrice = null;
            if(PurchasePrice().HasValue) 
                purchasePrice = Convert.ToInt32(tbPurchasePriceEstimated.Value.Value);

            // SaveToDb
            DataAccess da = new DataAccess();
            da.ExecuteProcedure("updateNetSheetFields",
                new string[]
                {
                    "@PropertyId", "@BuyerPurchasePrice", "@RehabCosts", "@NetProfit", "@PurchasePrice",

                    "@NetSheet_LockChange_Estimated", "@NetSheet_LockChange_Actual", "@NetSheet_LockChange_WithLoan",
	                "@NetSheet_ReserveForUtilites_Estimated", "@NetSheet_ReserveForUtilites_Actual", "@NetSheet_ReserveForUtilites_WithLoan",
	                "@NetSheet_Insurance_Estimated", "@NetSheet_Insurance_Actual", "@NetSheet_Insurance_WithLoan", 
	                "@NetSheet_CashForKeys_Estimated", "@NetSheet_CashForKeys_Actual", "@NetSheet_CashForKeys_WithLoan", 
	                "@NetSheet_Miscellaenous_Estimated", "@NetSheet_Miscellaenous_Actual", "@NetSheet_Miscellaenous_WithLoan",
	                "@NetSheet_BrokerCoop", "@NetSheet_SellerClosingIncentives", "@NetSheet_EscrowClosingEstimate",
	                "@NetSheet_SellersCostRepairs_Estimated", "@NetSheet_SellersCostRepairs_Actual", "@NetSheet_SellersCostRepairs_WithLoan",
	                "@NetSheet_HomeWarranty_Estimated", "@NetSheet_HomeWarranty_Actual", "@NetSheet_HomeWarranty_WithLoan",
	                "@NetSheet_Appraisal_Estimated", "@NetSheet_Appraisal_Actual", "@NetSheet_Appraisal_WithLoan",
	                "@NetSheet_MiscellaneousClosingCosts_Estimated", "@NetSheet_MiscellaneousClosingCosts_Actual", "@NetSheet_MiscellaneousClosingCosts_WithLoan",
	                "@NetSheet_HOADues_Estimated", "@NetSheet_HOADues_Actual", "@NetSheet_HOADues_WithLoan",
	                "@NetSheet_TransactionFee_Estimated", "@NetSheet_TransactionFee_Actual", "@NetSheet_TransactionFee_WithLoan",
	                "@NetSheet_Other_Estimated", "@NetSheet_Other_Actual", "@NetSheet_Other_WithLoan",
	                "@NetSheet_FeeLCH", 
                    "@NetSheet_NonProfitFee", "@NetSheet_ClosingCosts", "@NetSheet_LoanBrokerFee", 
                    "@NetSheet_LoanFee", "@NetSheet_LoanInterest",
                    "@NetSheet_RehabCost_Estimated", "@NetSheet_RehabCost_Actual", "@NetSheet_RehabCost_WithLoan", 
                    "@NetSheet_1stDOT", "@NetSheet_DownPayment",
                    "@NetSheet_SellingPrice", "@NetSheet_MortgageType", "@NetSheet_EstClosingDate", "@NetSheet_WithLoan",
                    "@NetSheet_ClosingCosts_Estimated", "@NetSheet_ClosingCosts_Actual", "@NetSheet_ClosingCosts_WithLoan",
                    "@NetSheet_Manual_ClosingCosts_Estimated", "@NetSheet_Manual_ClosingCosts_Actual", "@NetSheet_Manual_ClosingCosts_WithLoan", 
                    
                    "@NetSheet_NonProfitFee_Estimated", "@NetSheet_NonProfitFee_Actual", "@NetSheet_NonProfitFee_WithLoan",
                    "@NetSheet_Manual_NonProfitFee_Estimated", "@NetSheet_Manual_NonProfitFee_Actual", "@NetSheet_Manual_NonProfitFee_WithLoan", 
                    "@NetSheet_Broker_Estimated", "@NetSheet_Broker_Actual", "@NetSheet_Broker_WithLoan",
                    "@NetSheet_Manual_Broker_Estimated", "@NetSheet_Manual_Broker_Actual", "@NetSheet_Manual_Broker_WithLoan", 
                    "@NetSheet_SellerClosing_Estimated", "@NetSheet_SellerClosing_Actual", "@NetSheet_SellerClosing_WithLoan",
                    "@NetSheet_Manual_SellerClosing_Estimated", "@NetSheet_Manual_SellerClosing_Actual", "@NetSheet_Manual_SellerClosing_WithLoan", 
                    "@NetSheet_Escrow_Estimated", "@NetSheet_Escrow_Actual", "@NetSheet_Escrow_WithLoan",
                    "@NetSheet_Manual_Escrow_Estimated", "@NetSheet_Manual_Escrow_Actual", "@NetSheet_Manual_Escrow_WithLoan", 

                    "@NetSheet_ProfitParticipation_Estimated", "@NetSheet_ProfitParticipation_Actual", "@NetSheet_ProfitParticipation_WithLoan",
                    "@NetSheet_Manual_ProfitParticipation_Estimated", "@NetSheet_Manual_ProfitParticipation_Actual", "@NetSheet_Manual_ProfitParticipation_WithLoan", 
                    "@NetSheet_LoanFeeSumma", "@NetSheet_Manual_LoanFee", "@NetSheet_LoanInterestSumma", "@NetSheet_Manual_LoanInterest",

                    "@NetSheet_PurchasePrice_Actual", "@NetSheet_Manual_PurchasePrice_Actual", "@NetSheet_PurchasePrice_WithLoan", "@NetSheet_Manual_PurchasePrice_WithLoan",
	                "@NetSheet_Total_AcquisitionCost_Estimated", "@NetSheet_Manual_Total_AcquisitionCost_Estimated", "@NetSheet_Total_AcquisitionCost_Actual", "@NetSheet_Manual_Total_AcquisitionCost_Actual", "@NetSheet_Total_AcquisitionCost_WithLoan", "@NetSheet_Manual_Total_AcquisitionCost_WithLoan", 
                    "@NetSheet_Total_PreparationCost_Estimated", "@NetSheet_Manual_Total_PreparationCost_Estimated", "@NetSheet_Total_PreparationCost_Actual", "@NetSheet_Manual_Total_PreparationCost_Actual", "@NetSheet_Total_PreparationCost_WithLoan", "@NetSheet_Manual_Total_PreparationCost_WithLoan", 
	                "@NetSheet_Total_PresaleCost_Estimated", "@NetSheet_Manual_Total_PresaleCost_Estimated", "@NetSheet_Total_PresaleCost_Actual", "@NetSheet_Manual_Total_PresaleCost_Actual", "@NetSheet_Total_PresaleCost_WithLoan", "@NetSheet_Manual_Total_PresaleCost_WithLoan",
                    "@NetSheet_SellingPrice_Estimated", "@NetSheet_Manual_SellingPrice_Estimated", "@NetSheet_SellingPrice_Actual", "@NetSheet_Manual_SellingPrice_Actual", "@NetSheet_SellingPrice_WithLoan", "@NetSheet_Manual_SellingPrice_WithLoan", 
                    "@NetSheet_Total_ClosingCost_Estimated", "@NetSheet_Manual_Total_ClosingCost_Estimated", "@NetSheet_Total_ClosingCost_Actual", "@NetSheet_Manual_Total_ClosingCost_Actual", "@NetSheet_Total_ClosingCost_WithLoan", "@NetSheet_Manual_Total_ClosingCost_WithLoan",
                    "@NetSheet_Note", "@NetSheet_Manual_Note", "@NetSheet_2ndDOT", "@NetSheet_Manual_2ndDOT", "@NetSheet_Total_LoanCost", "@NetSheet_Manual_Total_LoanCost",
	                "@NetSheet_NetProfit_Estimated", "@NetSheet_Manual_NetProfit_Estimated", "@NetSheet_NetProfit_Actual", "@NetSheet_Manual_NetProfit_Actual", "@NetSheet_NetProfit_WithLoan", "@NetSheet_Manual_NetProfit_WithLoan",
                    "@NetSheet_Total_Equity_Estimated", "@NetSheet_Manual_Total_Equity_Estimated", "@NetSheet_Total_Equity_Actual", "@NetSheet_Manual_Total_Equity_Actual", "@NetSheet_Total_Equity_WithLoan", "@NetSheet_Manual_Total_Equity_WithLoan",
                    "@NetSheet_Total_NetProfit_Estimated", "@NetSheet_Manual_Total_NetProfit_Estimated", "@NetSheet_Total_NetProfit_Actual", "@NetSheet_Manual_Total_NetProfit_Actual", "@NetSheet_Total_NetProfit_WithLoan", "@NetSheet_Manual_Total_NetProfit_WithLoan",
	                "@NetSheet_Total_NetPercent_Estimated", "@NetSheet_Manual_Total_NetPercent_Estimated", "@NetSheet_Total_NetPercent_Actual", "@NetSheet_Manual_Total_NetPercent_Actual", "@NetSheet_Total_NetPercent_WithLoan", "@NetSheet_Manual_Total_NetPercent_WithLoan",


                    "@NetSheet_Occupancy",
                },
                new object[]
                {
                    GetPropertyId(), buyerPurchasePrice, Convert.ToInt32(tbRehabCostEstimated.Value.Value), chbWithLoan.Checked?Convert.ToInt32(tbTotalNetProfitLoan.Value.Value):Convert.ToInt32(tbTotalNetProfitActual.Value.Value), purchasePrice,
                    tbLockEstimated.Value??0, tbLockActual.Value??0, tbLockLoan.Value??0,
                    tbReserveEstimated.Value??0, tbReserveActual.Value??0, tbReserveLoan.Value??0,
                    tbInsuranceEstimated.Value??0, tbInsuranceActual.Value??0, tbInsuranceLoan.Value??0,
                    tbCashEstimated.Value??0, tbCashActual.Value??0, tbCashLoan.Value??0,
                    tbMiscEstimated.Value??0, tbMiscActual.Value??0, tbMiscLoan.Value??0,
                    tbBrokerPercent.Value??0, tbSellerPercent.Value??0, tbEscrowPercent.Value??0, 
                    tbSellersCostEstimated.Value??0, tbSellersCostActual.Value??0, tbSellersCostLoan.Value??0,
                    tbHomeEstimated.Value??0, tbHomeActual.Value??0, tbHomeLoan.Value??0,
                    tbAppraisalEstimated.Value??0, tbAppraisalActual.Value??0, tbAppraisalLoan.Value??0,
                    tbMiscClosingEstimated.Value??0, tbMiscClosingActual.Value??0, tbMiscClosingLoan.Value??0,
                    tbHoaEstimated.Value??0, tbHoaActual.Value??0, tbHoaLoan.Value??0,
                    tbTransactionEstimated.Value??0, tbTransactionActual.Value??0, tbTransactionLoan.Value??0,
                    tbOtherEstimated.Value??0, tbOtherActual.Value??0, tbOtherLoan.Value??0,
                    tbProfitParticipationPercent.Value??0,
                    tbNonProfitFeePercent.Value??0, tbClosingCostsPercent.Value??0,
                    tbLoanBrokerFee.Value??0, tbLoanFeePercent.Value??0, tbLoanInterestPercent.Value??0,
                    tbRehabCostEstimated.Value??0, tbRehabCostActual.Value??0, tbRehabCostLoan.Value??0,
                    tb1stDOT.Value??0, tbDownPayment.Value??0, tbSellingPrice.Value??0, cbMortgageType.SelectedValue, tbEstClosingDate.Text, chbWithLoan.Checked,
                    tbClosingCostsEstimated.Value??0, tbClosingCostsActual.Value??0, tbClosingCostsLoan.Value??0,
                    chbManualClosingCostsEstimated.Checked, chbManualClosingCostsActual.Checked, chbManualClosingCostsLoan.Checked,

                    tbNonProfitFeeEstimated.Value??0, tbNonProfitFeeActual.Value??0, tbNonProfitFeeLoan.Value??0,
                    chbManualNonProfitFeeEstimated.Checked, chbManualNonProfitFeeActual.Checked, chbManualNonProfitFeeLoan.Checked,
                    tbBrokerEstimated.Value??0, tbBrokerActual.Value??0, tbBrokerLoan.Value??0,
                    chbManualBrokerEstimated.Checked, chbManualBrokerActual.Checked, chbManualBrokerLoan.Checked,
                    tbSellerClosingEstimated.Value??0, tbSellerClosingActual.Value??0, tbSellerClosingLoan.Value??0,
                    chbManualSellerClosingEstimated.Checked, chbManualSellerClosingActual.Checked, chbManualSellerClosingLoan.Checked,
                    tbEscrowEstimated.Value??0, tbEscrowActual.Value??0, tbEscrowLoan.Value??0,
                    chbManualEscrowEstimated.Checked, chbManualEscrowActual.Checked, chbManualEscrowLoan.Checked,

                    tbProfitParticipationEstimated.Value??0, tbProfitParticipationActual.Value??0, tbProfitParticipationLoan.Value??0,
                    chbManualProfitParticipationEstimated.Checked, chbManualProfitParticipationActual.Checked, chbManualProfitParticipationLoan.Checked,
                    tbLoanFeeCalculated.Value??0, chbManualLoanFee.Checked,
                    tbLoanInterestCalculated.Value??0, chbManualLoanInterest.Checked,

                    tbPurchasePriceActual.Value??0, chbManualPurchasePriceActual.Checked, tbPurchasePriceLoan.Value??0, chbManualPurchasePriceLoan.Checked,
	                tbAcquisitionEstimated.Value??0, chbManualAcquisitionEstimated.Checked, tbAcquisitionActual.Value??0, chbManualAcquisitionActual.Checked, tbAcquisitionLoan.Value??0, chbManualAcquisitionLoan.Checked, 
                    tbPreparationEstimated.Value??0, chbManualPreparationEstimated.Checked, tbPreparationActual.Value??0, chbManualPreparationActual.Checked, tbPreparationLoan.Value??0, chbManualPreparationLoan.Checked, 
	                tbTotalPresaleEstimated.Value??0, chbManualTotalPresaleEstimated.Checked, tbTotalPresaleActual.Value??0, chbManualTotalPresaleActual.Checked, tbTotalPresaleLoan.Value??0, chbManualTotalPresaleLoan.Checked,
                    tbSellingPriceEstimated.Value??0, chbManualSellingPriceEstimated.Checked, tbSellingPriceActual.Value??0, chbManualSellingPriceActual.Checked, tbSellingPriceLoan.Value??0, chbManualSellingPriceLoan.Checked, 
                    tbTotalClosingCostsEstimated.Value??0, chbManualTotalClosingCostsEstimated.Checked, tbTotalClosingCostsActual.Value??0, chbManualTotalClosingCostsActual.Checked, tbTotalClosingCostsLoan.Value??0, chbManualTotalClosingCostsLoan.Checked,
                    tbPurchasePlusRehab.Value??0, chbManualPurchasePlusRehab.Checked, tb2ndDOT.Value??0, chbManual2ndDOT.Checked, tbLoanTotal.Value??0, chbManualLoanTotal.Checked,
	                tbNetProfitEstimated.Value??0, chbManualNetProfitEstimated.Checked, tbNetProfitActual.Value??0, chbManualNetProfitActual.Checked, tbNetProfitLoan.Value??0, chbManualNetProfitLoan.Checked,
                    tbEquityEstimated.Value??0, chbManualEquityEstimated.Checked, tbEquityActual.Value??0, chbManualEquityActual.Checked, tbEquityLoan.Value??0, chbManualEquityLoan.Checked,
                    tbTotalNetProfitEstimated.Value??0, chbManualTotalNetProfitEstimated.Checked, tbTotalNetProfitActual.Value??0, chbManualTotalNetProfitActual.Checked, tbTotalNetProfitLoan.Value??0, chbManualTotalNetProfitLoan.Checked,
	                tbTotalNetPercentEstimated.Value??0, chbManualTotalNetPercentEstimated.Checked, tbTotalNetPercentActual.Value??0, chbManualTotalNetPercentActual.Checked, tbTotalNetPercentLoan.Value??0, chbManualTotalNetPercentLoan.Checked,
                    
                    ddlOccupancy.SelectedValue
                });

            ScriptManager.RegisterStartupScript(this, this.GetType(), "key",
                string.Format("CloseAndRefresh('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}');", 
                ((int)Session["NetProfit"]).ToString("c"), 
                tbSellingPrice.Value.Value.ToString("c"), tbSellingPrice.Value.Value.ToString(), 
                tbRehabCostEstimated.Value.Value.ToString("c"), tbRehabCostEstimated.Value.Value.ToString(),
                tbPurchasePriceEstimated.Value.Value.ToString("c"), tbPurchasePriceEstimated.Value.Value.ToString()), true);

        }

        private void Calculate()
        {
            #region AcquisitionCost
            if (!chbManualPurchasePriceActual.Checked)
                tbPurchasePriceActual.Value = tbPurchasePriceEstimated.Value;
            if (!chbManualPurchasePriceLoan.Checked)
                tbPurchasePriceLoan.Value = tbPurchasePriceActual.Value;

            if (!chbManualNonProfitFeeEstimated.Checked)
                tbNonProfitFeeEstimated.Value = tbPurchasePriceEstimated.Value * tbNonProfitFeePercent.Value / 100;
            if (!chbManualNonProfitFeeActual.Checked)
                tbNonProfitFeeActual.Value = tbPurchasePriceActual.Value * tbNonProfitFeePercent.Value / 100;
            if (!chbManualNonProfitFeeLoan.Checked)
                tbNonProfitFeeLoan.Value = tbPurchasePriceLoan.Value * tbNonProfitFeePercent.Value / 100;
            
            if (!chbManualClosingCostsEstimated.Checked)
                tbClosingCostsEstimated.Value = tbPurchasePriceEstimated.Value * tbClosingCostsPercent.Value / 100;
            if (!chbManualClosingCostsActual.Checked)
                tbClosingCostsActual.Value = tbPurchasePriceActual.Value * tbClosingCostsPercent.Value / 100;
            if (!chbManualClosingCostsLoan.Checked)
                tbClosingCostsLoan.Value = tbPurchasePriceLoan.Value * tbClosingCostsPercent.Value / 100;

            if (!chbManualAcquisitionEstimated.Checked)
                tbAcquisitionEstimated.Value = tbPurchasePriceEstimated.Value + tbNonProfitFeeEstimated.Value + tbClosingCostsEstimated.Value;
            if (!chbManualAcquisitionActual.Checked)
                tbAcquisitionActual.Value = tbPurchasePriceActual.Value + tbNonProfitFeeActual.Value + tbClosingCostsActual.Value;
            if (!chbManualAcquisitionLoan.Checked)
                tbAcquisitionLoan.Value = tbPurchasePriceLoan.Value + tbNonProfitFeeLoan.Value + tbClosingCostsLoan.Value;
            #endregion

            if (!chbManualPreparationEstimated.Checked)
                tbPreparationEstimated.Value = tbRehabCostEstimated.Value + tbLockEstimated.Value + tbReserveEstimated.Value + tbInsuranceEstimated.Value + tbCashEstimated.Value + tbMiscEstimated.Value;
            if (!chbManualPreparationActual.Checked)
                tbPreparationActual.Value = tbRehabCostActual.Value + tbLockActual.Value + tbReserveActual.Value + tbInsuranceActual.Value + tbCashActual.Value + tbMiscActual.Value;
            if (!chbManualPreparationLoan.Checked)
                tbPreparationLoan.Value = tbRehabCostLoan.Value + tbLockLoan.Value + tbReserveLoan.Value + tbInsuranceLoan.Value + tbCashLoan.Value + tbMiscLoan.Value;
            
            if (!chbManualTotalPresaleEstimated.Checked)
                tbTotalPresaleEstimated.Value = tbAcquisitionEstimated.Value + tbPreparationEstimated.Value;
            if (!chbManualTotalPresaleActual.Checked)
                tbTotalPresaleActual.Value = tbAcquisitionActual.Value + tbPreparationActual.Value;
            if (!chbManualTotalPresaleLoan.Checked)
                tbTotalPresaleLoan.Value = tbAcquisitionLoan.Value + tbPreparationLoan.Value;

            #region ClosingCost
            if (!chbManualSellingPriceEstimated.Checked)
                tbSellingPriceEstimated.Value = tbSellingPrice.Value;
            if (!chbManualSellingPriceActual.Checked)
                tbSellingPriceActual.Value = tbSellingPrice.Value;
            if (!chbManualSellingPriceLoan.Checked)
                tbSellingPriceLoan.Value = tbSellingPrice.Value;

            if (!chbManualBrokerEstimated.Checked)
                tbBrokerEstimated.Value = tbSellingPriceEstimated.Value * tbBrokerPercent.Value / 100;
            if (!chbManualBrokerActual.Checked)
                tbBrokerActual.Value = tbSellingPriceActual.Value * tbBrokerPercent.Value / 100;
            if (!chbManualBrokerLoan.Checked)
                tbBrokerLoan.Value = tbSellingPriceLoan.Value * tbBrokerPercent.Value / 100;
            
            if (!chbManualSellerClosingEstimated.Checked)
                tbSellerClosingEstimated.Value = tbSellingPriceEstimated.Value * tbSellerPercent.Value / 100;
            if (!chbManualSellerClosingActual.Checked)
                tbSellerClosingActual.Value = tbSellingPriceActual.Value * tbSellerPercent.Value / 100;
            if (!chbManualSellerClosingLoan.Checked)
                tbSellerClosingLoan.Value = tbSellingPriceLoan.Value * tbSellerPercent.Value / 100;
            
            if (!chbManualEscrowEstimated.Checked)
                tbEscrowEstimated.Value = tbSellingPriceEstimated.Value * tbEscrowPercent.Value / 100;
            if (!chbManualEscrowActual.Checked)
                tbEscrowActual.Value = tbSellingPriceActual.Value * tbEscrowPercent.Value / 100;
            if (!chbManualEscrowLoan.Checked)
                tbEscrowLoan.Value = tbSellingPriceLoan.Value * tbEscrowPercent.Value / 100;

            if (!chbManualTotalClosingCostsEstimated.Checked)
                tbTotalClosingCostsEstimated.Value = tbBrokerEstimated.Value + tbSellerClosingEstimated.Value + tbEscrowEstimated.Value +
                                                    tbSellersCostEstimated.Value + tbHomeEstimated.Value + tbAppraisalEstimated.Value +
                                                    tbMiscClosingEstimated.Value + tbHoaEstimated.Value + tbTransactionEstimated.Value + tbOtherEstimated.Value;
            if (!chbManualTotalClosingCostsActual.Checked)
                tbTotalClosingCostsActual.Value = tbBrokerActual.Value + tbSellerClosingActual.Value + tbEscrowActual.Value +
                                                    tbSellersCostActual.Value + tbHomeActual.Value + tbAppraisalActual.Value +
                                                    tbMiscClosingActual.Value + tbHoaActual.Value + tbTransactionActual.Value + tbOtherActual.Value;
            if (!chbManualTotalClosingCostsLoan.Checked)
                tbTotalClosingCostsLoan.Value = tbBrokerLoan.Value + tbSellerClosingLoan.Value + tbEscrowLoan.Value +
                                                tbSellersCostLoan.Value + tbHomeLoan.Value + tbAppraisalLoan.Value +
                                                tbMiscClosingLoan.Value + tbHoaLoan.Value + tbTransactionLoan.Value + tbOtherLoan.Value;
            #endregion

            #region LoanCost
            if (!chbManualPurchasePlusRehab.Checked)
                tbPurchasePlusRehab.Value = tbAcquisitionEstimated.Value + tbRehabCostEstimated.Value;
            if (!chbManual2ndDOT.Checked)
                tb2ndDOT.Value = tbPurchasePlusRehab.Value - tb1stDOT.Value;
            if (!chbManualLoanFee.Checked)
                tbLoanFeeCalculated.Value = tb1stDOT.Value * tbLoanFeePercent.Value / 100;
            if (!chbManualLoanInterest.Checked)
                tbLoanInterestCalculated.Value = tb1stDOT.Value * tbLoanInterestPercent.Value / 100;
            if (!chbManualLoanTotal.Checked)
                tbLoanTotal.Value = tbLoanBrokerFee.Value + tbLoanFeeCalculated.Value + tbLoanInterestCalculated.Value;
            #endregion

            #region NetProfit
            if (!chbManualNetProfitEstimated.Checked)
                tbNetProfitEstimated.Value = tbSellingPriceEstimated.Value - tbTotalPresaleEstimated.Value - tbTotalClosingCostsEstimated.Value;
            if (!chbManualNetProfitActual.Checked)
                tbNetProfitActual.Value = tbSellingPriceActual.Value - tbTotalPresaleActual.Value - tbTotalClosingCostsActual.Value;
            if (!chbManualNetProfitLoan.Checked)
                tbNetProfitLoan.Value = tbSellingPriceLoan.Value - tbTotalPresaleLoan.Value - tbTotalClosingCostsLoan.Value - tbLoanTotal.Value;

            if (!chbManualProfitParticipationEstimated.Checked)
                tbProfitParticipationEstimated.Value = tbNetProfitEstimated.Value * tbProfitParticipationPercent.Value / 100;
            if (!chbManualProfitParticipationActual.Checked)
                tbProfitParticipationActual.Value = tbNetProfitActual.Value * tbProfitParticipationPercent.Value / 100;
            if (!chbManualProfitParticipationLoan.Checked)
                tbProfitParticipationLoan.Value = tbNetProfitLoan.Value * tbProfitParticipationPercent.Value / 100;

            if (!chbManualEquityEstimated.Checked)
                tbEquityEstimated.Value = tbTotalPresaleEstimated.Value;
            if (!chbManualEquityActual.Checked)
                tbEquityActual.Value = tbTotalPresaleActual.Value;
            if (!chbManualEquityLoan.Checked)
                tbEquityLoan.Value = tb2ndDOT.Value + tbLoanTotal.Value;

            if (!chbManualTotalNetProfitEstimated.Checked)
                tbTotalNetProfitEstimated.Value = Math.Round(tbNetProfitEstimated.Value??0 - tbProfitParticipationEstimated.Value??0, 0);
            if (!chbManualTotalNetProfitActual.Checked)
                tbTotalNetProfitActual.Value = Math.Round(tbNetProfitActual.Value??0 - tbProfitParticipationActual.Value??0);
            if (!chbManualTotalNetProfitLoan.Checked)
                tbTotalNetProfitLoan.Value = Math.Round(tbNetProfitLoan.Value??0 - tbProfitParticipationLoan.Value??0);

            if (!chbManualTotalNetPercentEstimated.Checked)
                tbTotalNetPercentEstimated.Value = tbTotalNetProfitEstimated.Value / (tbEquityEstimated.Value == 0 ? 1 : tbEquityEstimated.Value) * 100;
            if (!chbManualTotalNetPercentActual.Checked)
                tbTotalNetPercentActual.Value = tbTotalNetProfitActual.Value / (tbEquityActual.Value == 0 ? 1 : tbEquityActual.Value) * 100;
            if (!chbManualTotalNetPercentLoan.Checked)
                tbTotalNetPercentLoan.Value = tbTotalNetProfitLoan.Value / (tbEquityLoan.Value == 0 ? 1 : tbEquityLoan.Value) * 100;
            #endregion
        }

        int GetEstimatedValue()
        {
            int? purchasePrice = PurchasePrice();
            return purchasePrice.HasValue ? purchasePrice.Value : BuyerPurchasePrice().Value;
        }

        int? BuyerPurchasePrice()
        {
            DataAccess da = new DataAccess();
            DataTable tbl =
            da.ExecuteSQL("select BuyerPurchasePrice from LvReo_MasterList where PropertyId=" + GetPropertyId());
            if (!tbl.Rows[0].IsNull(0))
                return Utilities.StrToInt(tbl.Rows[0][0].ToString());
            else
                return 0;
        }

        int? PurchasePrice()
        {
            DataAccess da = new DataAccess();
            DataTable tbl =
            da.ExecuteSQL("select PurchasePrice from LvReo_MasterList where PropertyId=" + GetPropertyId());
            if (!tbl.Rows[0].IsNull(0))
                return Utilities.StrToInt(tbl.Rows[0][0].ToString());
            else
                return 0;
        }

        /*int? GetRehabCostsValue()
        {
            DataAccess da = new DataAccess();
            DataTable tbl =
                  da.ExecuteSQL("select RehabCosts from LvReo_MasterList where PropertyId=" + GetPropertyId());

            var str = tbl.Rows[0][0].ToString();
            str = str.Replace(',', '.').Split('.')[0];
            if (string.IsNullOrEmpty(str)) return 0;
            return Convert.ToInt32(str);
        }*/

        private int GetPropertyId()
        {
          var str = Request.QueryString["PropertyId"].ToString();
          return Convert.ToInt32(str);
        }

        private bool IsPrintView()
        {
            bool result = false;
            if (Request.QueryString["PrintView"] != null)
                if (Convert.ToInt32(Request.QueryString["PrintView"]) == 1)
                    result = true;
            return result;
        }

        private bool IsExcelView()
        {
            bool result = false;
            if (Request.QueryString["ExcelView"] != null)
                if (Convert.ToInt32(Request.QueryString["ExcelView"]) == 1)
                    result = true;
            return result;
        }

        private bool IsLoan()
        {
            bool result = false;
            if (Request.QueryString["IsLoan"] != null)
                if (Convert.ToInt32(Request.QueryString["IsLoan"]) == 1)
                    result = true;
            return result;
        }

        protected void tb1stDOT_TextChanged(object sender, EventArgs e)
        {
            Calculate();
        }

        protected void chbWithLoan_CheckedChanged(Object sender, EventArgs e)
        {
          RefreshLoan();
        }

        protected void RefreshLoan()
        {
          tbodyLoan.Visible = chbWithLoan.Checked;

          //tbNonProfitFeePercent.Visible = chbWithLoan.Checked;
          //tbClosingCostsPercent.Visible = chbWithLoan.Checked;

          tbPurchasePlusRehab.Visible = chbWithLoan.Checked;
          chbManualPurchasePlusRehab.Visible = chbWithLoan.Checked;
          tb1stDOT.Visible = chbWithLoan.Checked;
          lblPurchasePlusRehab.Visible = chbWithLoan.Checked;
          lbl1stDOT.Visible = chbWithLoan.Checked;


          lnkPrint.NavigateUrl = string.Format(printUrl, GetPropertyId().ToString(), chbWithLoan.Checked ? "1" : "0");
        }

        protected void RefreshLoan(bool isLoan)
        {
            chbWithLoan.Checked = isLoan;
            RefreshLoan();
        }

        protected void chbClosingCost_CheckedChanged(Object sender, EventArgs e)
        {
            RefreshClosingCost();
        }

        protected void RefreshClosingCost()
        {
            ////////////
            SetEditState(chbManualPurchasePriceActual.Checked, tbPurchasePriceActual);
            SetEditState(chbManualPurchasePriceLoan.Checked, tbPurchasePriceLoan);

            SetEditState(chbManualClosingCostsEstimated.Checked, tbClosingCostsEstimated);
            SetEditState(chbManualClosingCostsActual.Checked, tbClosingCostsActual);
            SetEditState(chbManualClosingCostsLoan.Checked, tbClosingCostsLoan);

            SetEditState(chbManualNonProfitFeeEstimated.Checked, tbNonProfitFeeEstimated);
            SetEditState(chbManualNonProfitFeeActual.Checked, tbNonProfitFeeActual);
            SetEditState(chbManualNonProfitFeeLoan.Checked, tbNonProfitFeeLoan);

            SetEditState(chbManualAcquisitionEstimated.Checked, tbAcquisitionEstimated);
            SetEditState(chbManualAcquisitionActual.Checked, tbAcquisitionActual);
            SetEditState(chbManualAcquisitionLoan.Checked, tbAcquisitionLoan);
            ////////////
            SetEditState(chbManualPreparationEstimated.Checked, tbPreparationEstimated);
            SetEditState(chbManualPreparationActual.Checked, tbPreparationActual);
            SetEditState(chbManualPreparationLoan.Checked, tbPreparationLoan);

            SetEditState(chbManualTotalPresaleEstimated.Checked, tbTotalPresaleEstimated);
            SetEditState(chbManualTotalPresaleActual.Checked, tbTotalPresaleActual);
            SetEditState(chbManualTotalPresaleLoan.Checked, tbTotalPresaleLoan);
            ////////////
            SetEditState(chbManualSellingPriceEstimated.Checked, tbSellingPriceEstimated);
            SetEditState(chbManualSellingPriceActual.Checked, tbSellingPriceActual);
            SetEditState(chbManualSellingPriceLoan.Checked, tbSellingPriceLoan);

            SetEditState(chbManualBrokerEstimated.Checked, tbBrokerEstimated);
            SetEditState(chbManualBrokerActual.Checked, tbBrokerActual);
            SetEditState(chbManualBrokerLoan.Checked, tbBrokerLoan);

            SetEditState(chbManualSellerClosingEstimated.Checked, tbSellerClosingEstimated);
            SetEditState(chbManualSellerClosingActual.Checked, tbSellerClosingActual);
            SetEditState(chbManualSellerClosingLoan.Checked, tbSellerClosingLoan);

            SetEditState(chbManualEscrowEstimated.Checked, tbEscrowEstimated);
            SetEditState(chbManualEscrowActual.Checked, tbEscrowActual);
            SetEditState(chbManualEscrowLoan.Checked, tbEscrowLoan);
            
            SetEditState(chbManualTotalClosingCostsEstimated.Checked, tbTotalClosingCostsEstimated);
            SetEditState(chbManualTotalClosingCostsActual.Checked, tbTotalClosingCostsActual);
            SetEditState(chbManualTotalClosingCostsLoan.Checked, tbTotalClosingCostsLoan);
            ////////////
            SetEditState(chbManualPurchasePlusRehab.Checked, tbPurchasePlusRehab);
            SetEditState(chbManual2ndDOT.Checked, tb2ndDOT);
            SetEditState(chbManualLoanFee.Checked, tbLoanFeeCalculated);
            SetEditState(chbManualLoanInterest.Checked, tbLoanInterestCalculated);
            SetEditState(chbManualLoanTotal.Checked, tbLoanTotal);
            ////////////
            SetEditState(chbManualNetProfitEstimated.Checked, tbNetProfitEstimated);
            SetEditState(chbManualNetProfitActual.Checked, tbNetProfitActual);
            SetEditState(chbManualNetProfitLoan.Checked, tbNetProfitLoan);

            SetEditState(chbManualProfitParticipationEstimated.Checked, tbProfitParticipationEstimated);
            SetEditState(chbManualProfitParticipationActual.Checked, tbProfitParticipationActual);
            SetEditState(chbManualProfitParticipationLoan.Checked, tbProfitParticipationLoan);

            SetEditState(chbManualEquityEstimated.Checked, tbEquityEstimated);
            SetEditState(chbManualEquityActual.Checked, tbEquityActual);
            SetEditState(chbManualEquityLoan.Checked, tbEquityLoan);

            SetEditState(chbManualTotalNetProfitEstimated.Checked, tbTotalNetProfitEstimated);
            SetEditState(chbManualTotalNetProfitActual.Checked, tbTotalNetProfitActual);
            SetEditState(chbManualTotalNetProfitLoan.Checked, tbTotalNetProfitLoan);

            SetEditState(chbManualTotalNetPercentEstimated.Checked, tbTotalNetPercentEstimated);
            SetEditState(chbManualTotalNetPercentActual.Checked, tbTotalNetPercentActual);
            SetEditState(chbManualTotalNetPercentLoan.Checked, tbTotalNetPercentLoan);
            
            Calculate();
        }

        private void SetEditState(bool allowEdit, RadNumericTextBox tb)
        {
            tb.ReadOnly = !allowEdit;

            tb.BackColor = !allowEdit ? Color.White : ColorTranslator.FromHtml("#99CC33");
            tb.NegativeStyle.BackColor = tb.BackColor;
            tb.FocusedStyle.BackColor = tb.BackColor;
            tb.HoveredStyle.BackColor = tb.BackColor;
            tb.ReadOnlyStyle.BackColor = Color.White;
        }
        
        protected void btnDownload_Click(object sender, EventArgs e)
        {
            ToExcel();
        }

        private void ToExcel()
        {
            Excel ex = Utilities.GetExcel();
            Worksheet ws = ex.Worksheets[0];
            int maxCol = 8;
            int row = 0;
            string data = "";
            string currFormat = "$#,##0.00;$-#,##0.00";
            string percentFormat = "0.00%";

            DataAccess da = new DataAccess();
            DataTable dt = da.GetDataTable("getNetSheet", new string[] { "@PropertyId" }, new object[] { GetPropertyId() });
            DataRow dr = dt.Rows[0];

            WriteExcelTitle(ws, "Lender’s Clearing House Las Vegas", row, 0, maxCol);
            ++row;

            WriteExcelCellValue(ws, "Property Address:", "", row, 0, 1, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToString(Session["PropertyLocation"]), "", row, 1, 8, TextAlignmentType.Left);
            ++row;

            WriteExcelCellValue(ws, "Buyer's Name:", "", row, 0, 1, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToString(Session["InvestorName"]), "", row, 1, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, "# Bedrooms/Baths", "", row, 3, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToString(Session["Bedrooms"]) + "/" + Convert.ToString(Session["Bathrooms"]), "", row, 5, 1, TextAlignmentType.Left);
            WriteExcelCellValue(ws, "Occupancy	", "", row, 6, 1, TextAlignmentType.Left);
            if (!dr.IsNull("NetSheet_Occupancy"))
                if ((dr["NetSheet_Occupancy"].ToString().ToLower() == "vacant") || (dr["NetSheet_Occupancy"].ToString().ToLower() == "occupied"))
                    data = dr["NetSheet_Occupancy"].ToString();
            WriteExcelCellValue(ws, data, "", row, 7, 1, TextAlignmentType.Left);
            ++row;

            WriteExcelCellValue(ws, "Selling Price:", "", row, 0, 1, TextAlignmentType.Left);
            string dblSellingPrice = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["BuyerPurchasePrice"]), currFormat, row, 1, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, "Square feet", "", row, 3, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToString(Session["TotalSqft"]), "", row, 5, 1, TextAlignmentType.Left);
            ++row;

            WriteExcelCellValue(ws, "Mortgage Type:", "", row, 0, 1, TextAlignmentType.Left);
            WriteExcelCellValue(ws, dr["NetSheet_MortgageType"].ToString(), "", row, 1, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, "Acquisition Date:", "", row, 3, 2, TextAlignmentType.Left);
            if (!dr.IsNull("NetSheet_AcquisitionDate"))
                WriteExcelCellValue(ws, Convert.ToDateTime(dr["NetSheet_AcquisitionDate"]), "MM/dd/yyyy", row, 5, 1, TextAlignmentType.Left);
            ++row;

            WriteExcelCellValue(ws, "Down Payment:", "", row, 0, 1, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_DownPayment"]), currFormat, row, 1, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, "Garage", "", row, 3, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToString(Session["GarageSqft"]), "", row, 5, 1, TextAlignmentType.Left);
            ++row;

            WriteExcelCellValue(ws, "Est closing date:", "", row, 0, 1, TextAlignmentType.Left);
            if (!dr.IsNull("NetSheet_EstClosingDate"))
                WriteExcelCellValue(ws, dr["NetSheet_EstClosingDate"].ToString(), "", row, 1, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, "Pool", "", row, 3, 2, TextAlignmentType.Left);
            WriteExcelCellValue(ws, Convert.ToString(Session["Pool"]), "", row, 5, 1, TextAlignmentType.Left);
            ++row;
            ++row;

            WriteExcelTitle(ws, "NET SHEET", row, 0, maxCol);
            ++row;

            WriteExcelCellValue(ws, "Purchase Detail", "", row, 0, 1, TextAlignmentType.Center);
            WriteExcelCellValue(ws, "%", "", row, 1, 1, TextAlignmentType.Center);
            WriteExcelCellValue(ws, "Estimated", "", row, 2, 1, TextAlignmentType.Center);
            WriteExcelCellValue(ws, "Actual", "", row, 3, 1, TextAlignmentType.Center);
            WriteExcelCellValue(ws, "With Loan", "", row, 4, 1, TextAlignmentType.Center);
            ++row;

            WriteExcelTitle(ws, "ACQUISITION COST", row, 0, 1);
            ++row;

            #region PurchasePrice
            WriteExcelCellValue(ws, "Purchase Price", "", row, 0, 1, TextAlignmentType.Left);
            string dblPurchasePriceEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["PurchasePrice"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblPurchasePriceActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_PurchasePrice_Actual"]))
                WriteExcelCellFormula(ws, dblPurchasePriceEstimated, currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_PurchasePrice_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblPurchasePriceLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_PurchasePrice_WithLoan"]))
                WriteExcelCellFormula(ws, dblPurchasePriceActual, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_PurchasePrice_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region NonProfitFee
            WriteExcelCellValue(ws, "Non-Profit Fee", "", row, 0, 1, TextAlignmentType.Left);
            string dblNonProfitFeePercent = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dt.Rows[0]["NetSheet_NonProfitFee"])/100, percentFormat, row, 1, 1, TextAlignmentType.Right);
            string dblNonProfitFeeEstimated = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NonProfitFee_Estimated"]))
            {
                dblNonProfitFeeEstimated = dblPurchasePriceEstimated  + "*" + dblNonProfitFeePercent;
                WriteExcelCellFormula(ws, dblNonProfitFeeEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_NonProfitFee_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
                dblNonProfitFeeEstimated = "C" + (row + 1).ToString();
            }
            string dblNonProfitFeeActual = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NonProfitFee_Actual"]))
            {
                dblNonProfitFeeActual = dblPurchasePriceActual + "*" + dblNonProfitFeePercent;
                WriteExcelCellFormula(ws, dblNonProfitFeeActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_NonProfitFee_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
                dblNonProfitFeeActual = "D" + (row + 1).ToString();
            }
            string dblNonProfitFeeLoan = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NonProfitFee_WithLoan"]))
            {
                dblNonProfitFeeLoan = dblPurchasePriceLoan + "*" + dblNonProfitFeePercent;
                WriteExcelCellFormula(ws, dblNonProfitFeeLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_NonProfitFee_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                dblNonProfitFeeLoan = "E" + (row + 1).ToString();
            }
            ++row;
            #endregion

            #region ClosingCostsFrontEnd
            WriteExcelCellValue(ws, "Closing Costs - Front End", "", row, 0, 1, TextAlignmentType.Left);
            string dblClosingCostsPercent = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dt.Rows[0]["NetSheet_ClosingCosts"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);
            string dblClosingCostsEstimated = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ClosingCosts_Estimated"]))
            {
                dblClosingCostsEstimated = dblPurchasePriceEstimated + "*" + dblClosingCostsPercent;
                WriteExcelCellFormula(ws, dblClosingCostsEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ClosingCosts_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
                dblNonProfitFeeEstimated = "C" + (row + 1).ToString();
            }
            string dblClosingCostsActual = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ClosingCosts_Actual"]))
            {
                dblClosingCostsActual = dblPurchasePriceActual + "*" + dblClosingCostsPercent;
                WriteExcelCellFormula(ws, dblClosingCostsActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ClosingCosts_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
                dblNonProfitFeeActual = "D" + (row + 1).ToString();
            }
            string dblClosingCostsLoan = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ClosingCosts_WithLoan"]))
            {
                dblClosingCostsLoan = dblPurchasePriceLoan + "*" + dblClosingCostsPercent;
                WriteExcelCellFormula(ws, dblClosingCostsLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ClosingCosts_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                dblClosingCostsLoan = "E" + (row + 1).ToString();
            }
            ++row;
            #endregion

            #region Total Acquis Cost
            ++row;
            WriteExcelCellValue(ws, "TOTAL", "", row, 0, 1, TextAlignmentType.Right);

            string dblAcquisitionEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_AcquisitionCost_Estimated"]))
                WriteExcelCellFormula(ws, string.Format("SUM(C{0}:C{1})", (row - 3).ToString(), row.ToString()), currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_AcquisitionCost_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblAcquisitionActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_AcquisitionCost_Actual"]))
                WriteExcelCellFormula(ws, string.Format("SUM(D{0}:D{1})", (row - 3).ToString(), row.ToString()), currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_AcquisitionCost_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblAcquisitionLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_AcquisitionCost_WithLoan"]))
                WriteExcelCellFormula(ws, string.Format("SUM(E{0}:E{1})", (row - 3).ToString(), row.ToString()), currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_AcquisitionCost_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            ++row;
            WriteExcelTitle(ws, "PREPARATION COST", row, 0, 1);
            ++row;

            #region RehabCost
            WriteExcelCellValue(ws, "Rehab Cost", "", row, 0, 1, TextAlignmentType.Left);
            string dblRehabCostEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["RehabCosts"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblRehabCostActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_RehabCost_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblRehabCostLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_RehabCost_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region LockChange
            WriteExcelCellValue(ws, "Lock Change & Secure Property", "", row, 0, 1, TextAlignmentType.Left);
            string dblLockEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LockChange_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblLockActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LockChange_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblLockLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LockChange_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region ReserveForUtilites
            WriteExcelCellValue(ws, "Reserve for Utilites", "", row, 0, 1, TextAlignmentType.Left);
            string dblReserveEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ReserveForUtilites_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblReserveActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ReserveForUtilites_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblReserveLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ReserveForUtilites_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Insurance
            WriteExcelCellValue(ws, "Insurance", "", row, 0, 1, TextAlignmentType.Left);
            string dblInsuranceEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Insurance_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblInsuranceActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Insurance_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblInsuranceLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Insurance_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region CashForKeys
            WriteExcelCellValue(ws, "Cash for Keys/ Eviction", "", row, 0, 1, TextAlignmentType.Left);
            string dblCashEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_CashForKeys_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblCashActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_CashForKeys_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblCashLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_CashForKeys_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Miscellaenous
            WriteExcelCellValue(ws, "Miscellaenous", "", row, 0, 1, TextAlignmentType.Left);
            string dblMiscEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Miscellaenous_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblMiscActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Miscellaenous_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblMiscLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Miscellaenous_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Total Preparation Cost
            ++row;
            WriteExcelCellValue(ws, "TOTAL", "", row, 0, 1, TextAlignmentType.Right);

            string dblPreparationEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PreparationCost_Estimated"]))
                WriteExcelCellFormula(ws, string.Format("SUM(C{0}:C{1})", (row - 6).ToString(), row.ToString()), currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_PreparationCost_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblPreparationActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PreparationCost_Actual"]))
                WriteExcelCellFormula(ws, string.Format("SUM(D{0}:D{1})", (row - 6).ToString(), row.ToString()), currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_PreparationCost_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblPreparationLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PreparationCost_WithLoan"]))
                WriteExcelCellFormula(ws, string.Format("SUM(E{0}:E{1})", (row - 6).ToString(), row.ToString()), currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_PreparationCost_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Total Presale Cost
            ++row;
            WriteExcelCellValue(ws, "TOTAL PRE-SALE COSTS", "", row, 0, 1, TextAlignmentType.Right);

            string dblTotalPresaleEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PresaleCost_Estimated"]))
                WriteExcelCellFormula(ws, dblAcquisitionEstimated + "+" + dblPreparationEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_PresaleCost_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblTotalPresaleActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PresaleCost_Actual"]))
                WriteExcelCellFormula(ws, dblAcquisitionActual + "+" + dblPreparationActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_PresaleCost_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblTotalPresaleLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_PresaleCost_WithLoan"]))
                WriteExcelCellFormula(ws, dblAcquisitionLoan + "+" + dblPreparationLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_PresaleCost_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            ++row;
            WriteExcelTitle(ws, "CLOSING COST", row, 0, 1);
            ++row;

            #region SellingPrice
            WriteExcelCellValue(ws, "Selling Price", "", row, 0, 1, TextAlignmentType.Left);

            string dblSellingPriceEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellingPrice_Estimated"]))
                WriteExcelCellFormula(ws, dblSellingPrice, currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellingPrice_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblSellingPriceActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellingPrice_Actual"]))
                WriteExcelCellFormula(ws, dblSellingPrice, currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellingPrice_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblSellingPriceLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellingPrice_WithLoan"]))
                WriteExcelCellFormula(ws, dblSellingPrice, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellingPrice_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region BrokerCoop
            WriteExcelCellValue(ws, "Broker Coop", "", row, 0, 1, TextAlignmentType.Left);
            string dblBrokerPercent = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_BrokerCoop"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);
            string dblBrokerEstimated = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Broker_Estimated"]))
            {
                dblBrokerEstimated = dblSellingPriceEstimated + "*" + dblBrokerPercent;
                WriteExcelCellFormula(ws, dblBrokerEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Broker_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
                dblBrokerEstimated = "C" + (row + 1).ToString();
            }
            string dblBrokerActual = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Broker_Actual"]))
            {
                dblBrokerActual = dblSellingPriceActual + "*" + dblBrokerPercent;
                WriteExcelCellFormula(ws, dblBrokerActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Broker_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
                dblBrokerActual = "D" + (row + 1).ToString();
            }
            string dblBrokerLoan = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Broker_WithLoan"]))
            {
                dblBrokerLoan = dblSellingPriceLoan + "*" + dblBrokerPercent;
                WriteExcelCellFormula(ws, dblBrokerLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Broker_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                dblBrokerLoan = "E" + (row + 1).ToString();
            }
            ++row;
            #endregion

            #region SellerClosing
            WriteExcelCellValue(ws, "Seller Closing Incentives", "", row, 0, 1, TextAlignmentType.Left);
            string dblSellerPercent = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellerClosingIncentives"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);
            string dblSellerClosingEstimated = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellerClosing_Estimated"]))
            {
                dblSellerClosingEstimated = dblSellingPriceEstimated + "*" + dblSellerPercent;
                WriteExcelCellFormula(ws, dblSellerClosingEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellerClosing_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
                dblSellerClosingEstimated = "C" + (row + 1).ToString();
            }
            string dblSellerClosingActual = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellerClosing_Actual"]))
            {
                dblSellerClosingActual = dblSellingPriceActual + "*" + dblSellerPercent;
                WriteExcelCellFormula(ws, dblSellerClosingActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellerClosing_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
                dblSellerClosingActual = "D" + (row + 1).ToString();
            }
            string dblSellerClosingLoan = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_SellerClosing_WithLoan"]))
            {
                dblSellerClosingLoan = dblSellingPriceLoan + "*" + dblSellerPercent;
                WriteExcelCellFormula(ws, dblSellerClosingLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellerClosing_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                dblSellerClosingLoan = "E" + (row + 1).ToString();
            }
            ++row;
            #endregion

            #region Escrow
            WriteExcelCellValue(ws, "Escrow Closing Estimate", "", row, 0, 1, TextAlignmentType.Left);
            string dblEscrowPercent = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_EscrowClosingEstimate"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);
            string dblEscrowEstimated = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Escrow_Estimated"]))
            {
                dblEscrowEstimated = dblSellingPriceEstimated + "*" + dblEscrowPercent;
                WriteExcelCellFormula(ws, dblEscrowEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Escrow_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
                dblEscrowEstimated = "C" + (row + 1).ToString();
            }
            string dblEscrowActual = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Escrow_Actual"]))
            {
                dblEscrowActual = dblSellingPriceActual + "*" + dblEscrowPercent;
                WriteExcelCellFormula(ws, dblEscrowActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Escrow_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
                dblEscrowActual = "D" + (row + 1).ToString();
            }
            string dblEscrowLoan = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Escrow_WithLoan"]))
            {
                dblEscrowLoan = dblSellingPriceLoan + "*" + dblEscrowPercent;
                WriteExcelCellFormula(ws, dblEscrowLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Escrow_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                dblEscrowLoan = "E" + (row + 1).ToString();
            }
            ++row;
            #endregion

            #region SellersCost
            WriteExcelCellValue(ws, "Sellers Cost of Repairs", "", row, 0, 1, TextAlignmentType.Left);
            string dblSellersCostEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellersCostRepairs_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblSellersCostActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellersCostRepairs_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblSellersCostLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_SellersCostRepairs_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region HomeWarranty
            WriteExcelCellValue(ws, "Home Warranty", "", row, 0, 1, TextAlignmentType.Left);
            string dblHomeEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_HomeWarranty_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblHomeActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_HomeWarranty_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblHomeLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_HomeWarranty_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Appraisal
            WriteExcelCellValue(ws, "Appraisal", "", row, 0, 1, TextAlignmentType.Left);
            string dblAppraisalEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Appraisal_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblAppraisalActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Appraisal_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblAppraisalLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Appraisal_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region MiscClosing
            WriteExcelCellValue(ws, "Miscellaneous Closing Costs", "", row, 0, 1, TextAlignmentType.Left);
            string dblMiscClosingEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_MiscellaneousClosingCosts_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblMiscClosingActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_MiscellaneousClosingCosts_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblMiscClosingLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_MiscellaneousClosingCosts_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region HOADues
            WriteExcelCellValue(ws, "HOA Dues", "", row, 0, 1, TextAlignmentType.Left);
            string dblHoaEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_HOADues_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblHoaActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_HOADues_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblHoaLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_HOADues_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region TransactionFee
            WriteExcelCellValue(ws, "Transaction Fee", "", row, 0, 1, TextAlignmentType.Left);
            string dblTransactionEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_TransactionFee_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblTransactionActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_TransactionFee_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblTransactionLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_TransactionFee_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Other
            WriteExcelCellValue(ws, "Other", "", row, 0, 1, TextAlignmentType.Left);
            string dblOtherEstimated = "C" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Other_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblOtherActual = "D" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Other_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblOtherLoan = "E" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Other_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            #region Total Closing Costs
            ++row;
            WriteExcelCellValue(ws, "TOTAL CLOSING COSTS", "", row, 0, 1, TextAlignmentType.Right);

            string dblTotalClosingCostsEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_ClosingCost_Estimated"]))
                WriteExcelCellFormula(ws, string.Format("SUM(C{0}:C{1})", (row - 10).ToString(), row.ToString()), currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_ClosingCost_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblTotalClosingCostsActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_ClosingCost_Actual"]))
                WriteExcelCellFormula(ws, string.Format("SUM(D{0}:D{1})", (row - 10).ToString(), row.ToString()), currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_ClosingCost_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblTotalClosingCostsLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_ClosingCost_WithLoan"]))
                WriteExcelCellFormula(ws, string.Format("SUM(E{0}:E{1})", (row - 10).ToString(), row.ToString()), currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_ClosingCost_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            ++row;
            #endregion

            ++row;
            WriteExcelTitle(ws, "LOAN COST", row, 0, 4);
            string dblPurchasePlusRehab = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Note"]))
                WriteExcelCellFormula(ws, dblAcquisitionEstimated + "+" + dblRehabCostEstimated, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Note"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            WriteExcelCellValue(ws, "Note (Purchase + Rehab)", "", row, 5, 1, TextAlignmentType.Left);
            ++row;

            #region IsLoan Block
            //if (IsLoan())
            //{
                string dbl1stDOT = "E" + (row + 1).ToString();
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_1stDOT"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                WriteExcelCellValue(ws, "1st DOT", "", row, 5, 1, TextAlignmentType.Left);
                ++row;

                string dbl2ndDOT = "E" + (row + 1).ToString();
                if (!Convert.ToBoolean(dr["NetSheet_Manual_2ndDOT"]))
                    WriteExcelCellFormula(ws, dblPurchasePlusRehab + "-" + dbl1stDOT, currFormat, row, 4, 1, TextAlignmentType.Right);
                else
                    WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_2ndDOT"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                WriteExcelCellValue(ws, "2st DOT", "", row, 5, 1, TextAlignmentType.Left);
                ++row;

                string dblLoanBrokerFee = "E" + (row + 1).ToString();
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LoanBrokerFee"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                WriteExcelCellValue(ws, "Loan Broker Fee", "", row, 5, 1, TextAlignmentType.Left);
                ++row;

                string dblLoanFeePercent = "B" + (row + 1).ToString();
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LoanFee"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);
                string dblLoanFeeCalculated = "";
                if (!Convert.ToBoolean(dr["NetSheet_Manual_LoanFee"]))
                {
                    dblLoanFeeCalculated = dbl1stDOT + "*" + dblLoanFeePercent;
                    WriteExcelCellFormula(ws, dblLoanFeeCalculated, currFormat, row, 4, 1, TextAlignmentType.Right);
                }
                else
                {
                    WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LoanFeeSumma"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                    dblLoanFeeCalculated = "E" + (row + 1).ToString();
                }			  
                WriteExcelCellValue(ws, "Loan Fee", "", row, 5, 1, TextAlignmentType.Left);
                ++row;

                string dblLoanInterestPercent = "B" + (row + 1).ToString();
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LoanInterest"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);
                string dblLoanInterestCalculated = "";
                if (!Convert.ToBoolean(dr["NetSheet_Manual_LoanInterest"]))
                {
                    dblLoanInterestCalculated = dbl1stDOT + "*" + dblLoanInterestPercent;
                    WriteExcelCellFormula(ws, dblLoanInterestCalculated, currFormat, row, 4, 1, TextAlignmentType.Right);
                }
                else
                {
                    WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_LoanInterestSumma"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                    dblLoanInterestCalculated = "E" + (row + 1).ToString();
                }			  
                WriteExcelCellValue(ws, "Loan Interest (2mo)", "", row, 5, 1, TextAlignmentType.Left);
                ++row;

                ++row;
                WriteExcelCellValue(ws, "TOTAL LOAN COST", "", row, 0, 1, TextAlignmentType.Right);
                string dblLoanTotal = "E" + (row + 1).ToString();
                if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_LoanCost"]))
                    WriteExcelCellFormula(ws, dblLoanBrokerFee + "+" + dblLoanFeeCalculated + "+" + dblLoanInterestCalculated, currFormat, row, 4, 1, TextAlignmentType.Right);
                else
                    WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_LoanCost"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                ++row;
            //}
            #endregion

            #region NetProfit
            ++row;
            WriteExcelCellValue(ws, "NET PROFIT", "", row, 0, 1, TextAlignmentType.Right);

            string dblNetProfitEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NetProfit_Estimated"]))
                WriteExcelCellFormula(ws, dblSellingPriceEstimated + "-(" + dblTotalPresaleEstimated + ")-" + dblTotalClosingCostsEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_NetProfit_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblNetProfitActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NetProfit_Actual"]))
                WriteExcelCellFormula(ws, dblSellingPriceActual + "-(" + dblTotalPresaleActual + ")-" + dblTotalClosingCostsActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_NetProfit_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblNetProfitLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_NetProfit_WithLoan"]))
                WriteExcelCellFormula(ws, dblSellingPriceLoan + "-(" + dblTotalPresaleLoan + ")-" + dblTotalClosingCostsLoan + "-" + dblLoanTotal, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_NetProfit_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                
            ++row;
            #endregion

            #region FeeLCH
            WriteExcelCellValue(ws, "Fee to LCH Las Vegas", "", row, 0, 1, TextAlignmentType.Right);
            string dblProfitParticipationPercent = "B" + (row + 1).ToString();
            WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_FeeLCH"]) / 100, percentFormat, row, 1, 1, TextAlignmentType.Right);

            string dblProfitParticipationEstimated = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ProfitParticipation_Estimated"]))
            {
                dblProfitParticipationEstimated = dblNetProfitEstimated + "*" + dblProfitParticipationPercent;
                WriteExcelCellFormula(ws, dblProfitParticipationEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ProfitParticipation_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
                dblProfitParticipationEstimated = "C" + (row + 1).ToString();
            }

            string dblProfitParticipationActual = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ProfitParticipation_Actual"]))
            {
                dblProfitParticipationActual = dblNetProfitActual + "*" + dblProfitParticipationPercent;
                WriteExcelCellFormula(ws, dblProfitParticipationActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ProfitParticipation_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
                dblProfitParticipationActual = "C" + (row + 1).ToString();
            }

            string dblProfitParticipationLoan = "";
            if (!Convert.ToBoolean(dr["NetSheet_Manual_ProfitParticipation_WithLoan"]))
            {
                dblProfitParticipationLoan = dblNetProfitLoan + "*" + dblProfitParticipationPercent;
                WriteExcelCellFormula(ws, dblProfitParticipationLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            }
            else
            {
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_ProfitParticipation_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
                dblProfitParticipationLoan = "C" + (row + 1).ToString();
            }			  

            ++row;
            #endregion

            #region TotalEquity
            ++row;
            WriteExcelCellValue(ws, "TOTAL EQUITY", "", row, 0, 1, TextAlignmentType.Right);

            string dblEquityEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_Equity_Estimated"]))
                WriteExcelCellFormula(ws, dblTotalPresaleEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_Equity_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblEquityActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_Equity_Actual"]))
                WriteExcelCellFormula(ws, dblTotalPresaleActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_Equity_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblEquityLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_Equity_WithLoan"]))
                WriteExcelCellFormula(ws, dbl2ndDOT + "+" + dblLoanTotal, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_Equity_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);

            ++row;
            #endregion

            #region TotalNetProfit
            WriteExcelCellValue(ws, "TOTAL NET NET PROFIT", "", row, 0, 1, TextAlignmentType.Right);

            string dblTotalNetProfitEstimated = "C" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetProfit_Estimated"]))
                WriteExcelCellFormula(ws, dblNetProfitEstimated  + "-" + dblProfitParticipationEstimated, currFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_NetProfit_Estimated"]), currFormat, row, 2, 1, TextAlignmentType.Right);
            string dblTotalNetProfitActual = "D" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetProfit_Actual"]))
                WriteExcelCellFormula(ws, dblNetProfitActual + "-" + dblProfitParticipationActual, currFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_NetProfit_Actual"]), currFormat, row, 3, 1, TextAlignmentType.Right);
            string dblTotalNetProfitLoan = "E" + (row + 1).ToString();
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetProfit_WithLoan"]))
                WriteExcelCellFormula(ws, dblNetProfitLoan + "-" + dblProfitParticipationLoan, currFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_NetProfit_WithLoan"]), currFormat, row, 4, 1, TextAlignmentType.Right);
            
            ++row;
            #endregion

            #region TotalNetProfitPercent
            WriteExcelCellValue(ws, "Cash on cash return %", "", row, 0, 1, TextAlignmentType.Right);

            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetPercent_Estimated"]))
                WriteExcelCellFormula(ws, dblTotalNetProfitEstimated + "/" + "IF(" + dblEquityEstimated + "=0,1," + dblEquityEstimated + ")", percentFormat, row, 2, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_NetPercent_Estimated"]) / 100, percentFormat, row, 2, 1, TextAlignmentType.Right);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetPercent_Actual"]))
                WriteExcelCellFormula(ws, dblTotalNetProfitActual + "/" + "IF(" + dblEquityActual + "=0,1," + dblEquityActual + ")", percentFormat, row, 3, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_NetPercent_Actual"]) / 100, percentFormat, row, 3, 1, TextAlignmentType.Right);
            if (!Convert.ToBoolean(dr["NetSheet_Manual_Total_NetPercent_WithLoan"]))
                WriteExcelCellFormula(ws, dblTotalNetProfitLoan + "/" + "IF(" + dblEquityLoan + "=0,1," + dblEquityLoan + ")", percentFormat, row, 4, 1, TextAlignmentType.Right);
            else
                WriteExcelCellValue(ws, Convert.ToDouble(dr["NetSheet_Total_NetPercent_WithLoan"]) / 100, percentFormat, row, 4, 1, TextAlignmentType.Right);
            #endregion

            for (int i = 0; i < maxCol; ++i)
                ws.AutoFitColumn(i);

            string nameFile = "NetSheet.xls";
            if (!dr.IsNull("Apn"))
                nameFile = dr["Apn"].ToString() + ".xls";
            ex.Save(nameFile, FileFormatType.Excel2003, SaveType.OpenInExcel, HttpContext.Current.Response);
            HttpContext.Current.Response.End();
        }

        private void WriteExcelTitle(Worksheet ws, string text, int row, int col, int colCount)
        {
            ws.Cells[row, col].PutValue(text);
            ws.Cells.Merge(row, col, 1, colCount);
            ws.Cells[row, col].Style.Pattern = BackgroundType.Solid;
            ws.Cells[row, col].Style.ForegroundColor = Color.Silver;
            ws.Cells[row, col].Style.HorizontalAlignment = TextAlignmentType.Center;
            ws.Cells[row, col].Style.Font.Size = 10;
            ws.Cells[row, col].Style.Font.IsBold = true;
        }

        private void WriteExcelCellValue(Worksheet ws, object value, string format, int row, int col, int colCount, TextAlignmentType align)
        {
            ws.Cells[row, col].PutValue(value);
            if (!string.IsNullOrEmpty(format))
                ws.Cells[row, col].Style.Custom = format;
            if (colCount > 1)
                ws.Cells.Merge(row, col, 1, colCount);
            ws.Cells[row, col].Style.HorizontalAlignment = align;
            ws.Cells[row, col].Style.Font.Size = 8;
        }

        private void WriteExcelCellFormula(Worksheet ws, string formula, string format, int row, int col, int colCount, TextAlignmentType align)
        {
            ws.Cells[row, col].Formula = "=" + formula;
            if (!string.IsNullOrEmpty(format))
                ws.Cells[row, col].Style.Custom = format;
            if (colCount > 1)
                ws.Cells.Merge(row, col, 1, colCount);
            ws.Cells[row, col].Style.HorizontalAlignment = align;
            ws.Cells[row, col].Style.Font.Size = 8;
        }

    }
}
